/*/{Protheus.doc} SYNCLCT
E a User Function de geracao do layout LCT (Lancamentos Contabeis) do Sistema Synchro
@author Elton Teodoro Alves
@since 14/11/2014
@version P11 R5
@Param aParam,Array,Array enviado pelo schudele
@Example
Esta User Function foi desenvolvida para ser utilizada com o Schudele, que passa como parametro para User Function um array com a seguinte configuracao:</br>
</br>
<table>
<tr><th> POSICAO </th><th> TIPO </th><th> DESCRICAO             </th></tr>
<tr><td> [01]    </td><td> C    </td><td> Codigo da Empresa     </td></tr>
<tr><td> [02]    </td><td> C    </td><td> Codigo da Filial      </td></tr>
<tr><td> [03]    </td><td> C    </td><td> Codigo do Agendamento </td></tr>
<tr><td> [04]    </td><td> C    </td><td> Codigo da Tarefa      </td></tr>
</table>
</br>
Para teste fora do schudele, pode-se enviar para o SmartClient com a clausula -a estes dados separadas por virgula ',' Ex.: 99,01,0001,0001
/*/

#Define CRLF Chr(13) + Chr(10)
Static PERIOD := 'M'

#Include 'Protheus.ch'

User Function SYNCLCT(xParam)
	
	Local	cLayout		:=	Right(ProcName(),3)
	Local	aParam		:=	If(Valtype(xParam) == 'C',Separa(xParam,',',.T.),xParam)
	Local	aSyncEnv	:=	U_SYNCENV(aParam,cLayout,PERIOD)
	Local	cTrab		:=	GetNextAlias()
	Local	cTempTrab	:=	cLayout + Dtos(Date()) + StrTran(Time(),':','')
	Local	cTempFile	:=	GetSrvProfString("StartPath","") + cTempTrab + GetDbExtension()
	Local	cQueryCT2D	:=	''
	Local	cQueryCT2C	:=	''
	Local	cQuery		:=	''
	Local	cQueryBkp	:=	''
	Local	aStru		:=	{}
	Local	nX			:=	0
	Local	aFiliais	:=	StrTokArr(AllTrim(aSyncEnv[10]),'/',)
	Local	cFiliais	:=	''
	Local	nCount		:=	0
	Local	cPriDiaMes	:=	aSyncEnv[9] + '01'
	Local	cUltDiaMes	:=	DtoS(LastDate(StoD(cPriDiaMes)))
	Local	nTamHTSync	:=	0
	Local	nTamHTCT2	:=	0
	Local	nNumLinHT	:=	0
	Local	cHistEnc	:=	AllTrim(Getmv('MV_SYNC002'))
	Local	aOrigem		:=	{}
	
	// Monta a string com a lista de filiais para ser utilizada na query
	
	For nX := 1 To Len(aFiliais)
		
		cFiliais += "'" + aFiliais[nX] + "'"
		
		If nX # Len(aFiliais)
			
			cFiliais += ","
			
		End If
		
	Next nX
	
	// --------------------------------------------------------------
	// Monta estrutura do arquivo temporario com dados a serem exportados
	// --------------------------------------------------------------
	aAdd(aStru,{'POS0001','C',0006,0}) // CHAVE_LOTE
	aAdd(aStru,{'POS0007','C',0010,0}) // ORIGEM
	aAdd(aStru,{'POS0017','C',0040,0}) // CHAVE_ORIGEM
	aAdd(aStru,{'POS0057','C',0001,0}) // CTRL_INSTRUCAO
	aAdd(aStru,{'POS0058','C',0018,0}) // CODIGO_EST
	aAdd(aStru,{'POS0076','C',0008,0}) // DT_LANCAMENTO
	aAdd(aStru,{'POS0084','C',0015,0}) // CODIGO_ANALITICA
	aAdd(aStru,{'POS0099','C',0021,0}) // VL_LANCAMENTO
	aAdd(aStru,{'POS0120','C',0001,0}) // IND_DB_CR
	aAdd(aStru,{'POS0121','C',0018,0}) // CODIGO_CONTRA
	aAdd(aStru,{'POS0139','C',0018,0}) // CODIGO_OPERACAO
	aAdd(aStru,{'POS0157','C',0028,0}) // CODIGO_CUSTO
	aAdd(aStru,{'POS0185','C',0008,0}) // CODIGO_DESPESA
	aAdd(aStru,{'POS0193','C',0008,0}) // CODIGO_PADRAO
	aAdd(aStru,{'POS0201','C',0150,0}) // HIST_COMPLEMENTAR
	aAdd(aStru,{'POS0351','C',0050,0}) // NUM_LANCAMENTO
	aAdd(aStru,{'POS0401','C',0050,0}) // NUM_ARQUIVAMENTO
	aAdd(aStru,{'POS0451','C',0028,0}) // NOVO_CCUSTO
	aAdd(aStru,{'POS0479','C',0001,0}) // TIPO_LCTO
	aAdd(aStru,{'POS0480','C',0050,0}) // COD_PART
	aAdd(aStru,{'POS0530','C',0001,0}) // IDENT_LCTO
	
	nTamHTSync	:=	aStru[aScan(aStru,{|X| X[1] == 'POS0201'}),3] // Tamanho do Hitorico Contabil no Synchro
	nTamHTCT2	:=	GetSx3Cache('CT2_HIST','X3_TAMANHO') // Tamanho do Historico Contabil no Dicionario
	
	// Define o numero de linhas que serao concatenadas e enviadas ao layout
	nNumLinHT	+=	Int(nTamHTSync / nTamHTCT2)
	nNumLinHT	+=	If(nTamHTSync % nTamHTCT2 >= 1,1,0)
	
	// --------------------------------------------------------------
	
	If aSyncEnv[6]
		
		// --------------------------------------------------------------
		// Montagem da query de pesquisa
		// --------------------------------------------------------------
		cQueryCT2D += "SELECT " + CRLF
		cQueryCT2C += "SELECT " + CRLF
		
		cQueryCT2D += "CT2D.CT2_LOTE LOTE," + CRLF
		cQueryCT2C += "CT2C.CT2_LOTE LOTE," + CRLF
		
		cQueryCT2D += "CT2D.CT2_SBLOTE SUBLOTE," + CRLF
		cQueryCT2C += "CT2C.CT2_SBLOTE SUBLOTE," + CRLF
		
		cQueryCT2D += "CT2D.CT2_DOC DOC," + CRLF
		cQueryCT2C += "CT2C.CT2_DOC DOC," + CRLF
		
		cQueryCT2D += "CT2D.CT2_LINHA LINHA," + CRLF
		cQueryCT2C += "CT2C.CT2_LINHA LINHA," + CRLF
		
		cQueryCT2D += "CT2D.CT2_HP HIST_PADR," + CRLF
		cQueryCT2C += "CT2C.CT2_HP HIST_PADR," + CRLF
		
		cQueryCT2D += "CT2D.CT2_ORIGEM ORIGEM," + CRLF
		cQueryCT2C += "CT2C.CT2_ORIGEM ORIGEM," + CRLF
		
		//-------------------------------------------------------
		
		cQueryCT2D += "'" + cPriDiaMes + "' POS0001," + CRLF
		cQueryCT2C += "'" + cPriDiaMes + "' POS0001," + CRLF
		
		cQueryCT2D += "'DIXTAL' POS0007," + CRLF
		cQueryCT2C += "'DIXTAL' POS0007," + CRLF
		
		cQueryCT2D += "LTRIM(RTRIM(CT2D.CT2_ITEMD)) POS0017," + CRLF
		cQueryCT2C += "LTRIM(RTRIM(CT2C.CT2_ITEMC)) POS0017," + CRLF
		
		cQueryCT2D += "'I' POS0057," + CRLF
		cQueryCT2C += "'I' POS0057," + CRLF
		
		cQueryCT2D += "LTRIM(RTRIM(CT2D.CT2_ITEMD)) POS0058," + CRLF
		cQueryCT2C += "LTRIM(RTRIM(CT2C.CT2_ITEMC)) POS0058," + CRLF
		
		cQueryCT2D += "CT2D.CT2_DATA POS0076," + CRLF
		cQueryCT2C += "CT2C.CT2_DATA POS0076," + CRLF
		
		cQueryCT2D += "SUBSTRING(CT2D.CT2_DEBITO,7,7) POS0084," + CRLF
		cQueryCT2C += "SUBSTRING(CT2C.CT2_CREDIT,7,7) POS0084," + CRLF
		
		cQueryCT2D += "CT2D.CT2_VALOR POS0099," + CRLF
		cQueryCT2C += "CT2C.CT2_VALOR POS0099," + CRLF
		
		cQueryCT2D += "'D' POS0120," + CRLF
		cQueryCT2C += "'C' POS0120," + CRLF
		
		cQueryCT2D += "'' POS0121," + CRLF
		cQueryCT2C += "'' POS0121," + CRLF
		
		cQueryCT2D += "'' POS0139," + CRLF
		cQueryCT2C += "'' POS0139," + CRLF
		
		cQueryCT2D += "ZB4D.ZB4_CCUSTO POS0157," + CRLF
		cQueryCT2C += "ZB4C.ZB4_CCUSTO POS0157," + CRLF
		
		cQueryCT2D += "'' POS0185," + CRLF
		cQueryCT2C += "'' POS0185," + CRLF
		
		cQueryCT2D += "'' POS0193," + CRLF
		cQueryCT2C += "'' POS0193," + CRLF
		
		
		// ---------- LINHAS DO HISTORICO DO LANCAMENTO
		cQueryCT2D += "(" + CRLF
		cQueryCT2C += "(" + CRLF
		
		For nX := 1 To nNumLinHT
			
			cQueryCT2D += "ISNULL(( " + CRLF
			cQueryCT2D += "SELECT " + CRLF
			cQueryCT2D += "CT2DHIST.CT2_HIST " + CRLF
			cQueryCT2D += "FROM " + RetSqlName("CT2") + " CT2DHIST " + CRLF
			cQueryCT2D += "WHERE " + CRLF
			cQueryCT2D += "CT2DHIST.D_E_L_E_T_ <> '*' AND " + CRLF
			cQueryCT2D += "CT2DHIST.CT2_DATA = CT2D.CT2_DATA AND " + CRLF
			cQueryCT2D += "CT2DHIST.CT2_LOTE = CT2D.CT2_LOTE AND " + CRLF
			cQueryCT2D += "CT2DHIST.CT2_SBLOTE = CT2D.CT2_SBLOTE AND " + CRLF
			cQueryCT2D += "CT2DHIST.CT2_DOC = CT2D.CT2_DOC AND " + CRLF
			cQueryCT2D += "CT2DHIST.CT2_SEQLAN = CT2D.CT2_SEQLAN AND " + CRLF
			cQueryCT2D += "CT2DHIST.CT2_SEQHIS = '" + PadL(AllTrim(Str(nX)),GetSx3Cache('CT2_SEQHIS','X3_TAMANHO'),'0') + "'),'')" + CRLF
			
			cQueryCT2C += "ISNULL(( " + CRLF
			cQueryCT2C += "SELECT " + CRLF
			cQueryCT2C += "CT2CHIST.CT2_HIST " + CRLF
			cQueryCT2C += "FROM " + RetSqlName("CT2") + " CT2CHIST " + CRLF
			cQueryCT2C += "WHERE " + CRLF
			cQueryCT2C += "CT2CHIST.D_E_L_E_T_ <> '*' AND " + CRLF
			cQueryCT2C += "CT2CHIST.CT2_DATA = CT2C.CT2_DATA AND " + CRLF
			cQueryCT2C += "CT2CHIST.CT2_LOTE = CT2C.CT2_LOTE AND " + CRLF
			cQueryCT2C += "CT2CHIST.CT2_SBLOTE = CT2C.CT2_SBLOTE AND " + CRLF
			cQueryCT2C += "CT2CHIST.CT2_DOC = CT2C.CT2_DOC AND " + CRLF
			cQueryCT2C += "CT2CHIST.CT2_SEQLAN = CT2C.CT2_SEQLAN AND " + CRLF
			cQueryCT2C += "CT2CHIST.CT2_SEQHIS = '" + PadL(AllTrim(Str(nX)),GetSx3Cache('CT2_SEQHIS','X3_TAMANHO'),'0') + "'),'')" + CRLF
			
			If nX # nNumLinHT
				
				If TCGetDB() # 'ORACLE'
					
					cQueryCT2D += "+" + CRLF
					cQueryCT2C += "+" + CRLF
					
				Else
					
					cQueryCT2D += "||" + CRLF
					cQueryCT2C += "||" + CRLF
					
				End If
				
			End If
			
		Next nX
		
		cQueryCT2D += ") POS0201," + CRLF
		cQueryCT2C += ") POS0201," + CRLF
		
		//-------------------------------------------------
		
		cQueryCT2D += "'' POS0351," + CRLF
		cQueryCT2C += "'' POS0351," + CRLF
		
		cQueryCT2D += "'' POS0401," + CRLF
		cQueryCT2C += "'' POS0401," + CRLF
		
		cQueryCT2D += "ZB4D.ZB4_CCUSTO POS0451," + CRLF
		cQueryCT2C += "ZB4C.ZB4_CCUSTO POS0451," + CRLF
		
		cQueryCT2D += "'' POS0479," + CRLF
		cQueryCT2C += "'' POS0479," + CRLF
		
		cQueryCT2D += "CVCD.CVC_CGC POS0480," + CRLF
		cQueryCT2C += "CVCC.CVC_CGC POS0480," + CRLF
		
		cQueryCT2D += "'' POS0530" + CRLF
		cQueryCT2C += "'' POS0530" + CRLF
		
		//----- FROM CT2D CT2C
		
		cQueryCT2D += "FROM " + RetSqlName("CT2") + " CT2D " + CRLF
		cQueryCT2C += "FROM " + RetSqlName("CT2") + " CT2C " + CRLF
		
		//---- LEFT JOIN CT2D
		
		cQueryCT2D += "LEFT JOIN " + RetSqlName("CVC") + " CVCD ON " + CRLF
		cQueryCT2D += "CT2D.CT2_CODPAR = CVCD.CVC_CODPAR " + CRLF
		
		cQueryCT2D += "LEFT JOIN " + RetSqlName("ZB4") + " ZB4D ON " + CRLF
		cQueryCT2D += "RTRIM(LTRIM(CT2D.CT2_ITEMD)) = ZB4D.ZB4_FILIAL AND " + CRLF
		cQueryCT2D += "CT2D.CT2_CCD = ZB4D.ZB4_CCUSTO " + CRLF
		
		//---- LEFT JOIN CT2C
		
		cQueryCT2C += "LEFT JOIN " + RetSqlName("CVC") + " CVCC ON " + CRLF
		cQueryCT2C += "CT2C.CT2_CODPAR = CVCC.CVC_CODPAR " + CRLF
		
		cQueryCT2C += "LEFT JOIN " + RetSqlName("ZB4") + " ZB4C ON " + CRLF
		cQueryCT2C += "RTRIM(LTRIM(CT2C.CT2_ITEMC)) = ZB4C.ZB4_FILIAL AND " + CRLF
		cQueryCT2C += "CT2C.CT2_CCC = ZB4C.ZB4_CCUSTO " + CRLF
		
		//------ WHERE CT2D
		
		cQueryCT2D += "WHERE CT2D.D_E_L_E_T_ <> '*' AND " + CRLF
		cQueryCT2D += "CT2D.CT2_DC IN('1','2','3') AND " + CRLF
		cQueryCT2D += "CT2D.CT2_TPSALD = '1' AND " + CRLF
		cQueryCT2D += "CT2D.CT2_DATA >= '" + cPriDiaMes + "' AND  CT2D.CT2_DATA <= '" + cUltDiaMes + "' AND " + CRLF
		cQueryCT2D += "CT2D.CT2_DEBITO <> '" + Space(GetSx3Cache('CT2_DEBITO','X3_TAMANHO')) + "' AND " + CRLF
		cQueryCT2D += "CT2D.CT2_ITEMD IN(" + cFiliais + ") AND "
		cQueryCT2D += "ISNULL(CVCD.D_E_L_E_T_,' ') <> '*' AND " + CRLF
		cQueryCT2D += "ISNULL(ZB4D.D_E_L_E_T_,' ') <> '*' " + CRLF
		
		//------ WHERE CT2C
		
		cQueryCT2C += "WHERE CT2C.D_E_L_E_T_ <> '*' AND " + CRLF
		cQueryCT2C += "CT2C.CT2_DC IN('1','2','3') AND " + CRLF
		cQueryCT2C += "CT2C.CT2_TPSALD = '1' AND " + CRLF
		cQueryCT2C += "CT2C.CT2_DATA >= '" + cPriDiaMes + "' AND  CT2C.CT2_DATA <= '" + cUltDiaMes + "' AND " + CRLF
		cQueryCT2C += "CT2C.CT2_CREDIT <> '" + Space(GetSx3Cache('CT2_CREDIT','X3_TAMANHO')) + "' AND " + CRLF
		cQueryCT2C += "CT2C.CT2_ITEMC IN(" + cFiliais + ") AND "
		cQueryCT2C += "ISNULL(CVCC.D_E_L_E_T_,' ') <> '*' AND " + CRLF
		cQueryCT2C += "ISNULL(ZB4C.D_E_L_E_T_,' ') <> '*' " + CRLF
		
		// --------------------------------------------------------------
		
		
		cQuery	+=	cQueryCT2D
		cQuery	+=	"UNION ALL" + CRLF
		cQuery	+=	cQueryCT2C
		
		cQueryBkp	:=	cQuery
		
		cQuery := ChangeQuery(cQuery)
		
		If TcSqlExec(cQuery) < 0
			
			U_SYNCMAIL(AllTrim(aSyncEnv[15]),cLayout + ' - ' + AllTrim(aSyncEnv[17]),'Erro na execucao da query no banco de dados: ' + Chr(13) + Chr(10) + AllTrim(TcSqlError()),AllTrim(SubStr(aSyncEnv[4],1,6)),AllTrim(SubStr(aSyncEnv[4],7,6)),1,aSyncEnv[8],aSyncEnv[9])
			
		Else
			
			DbUseArea(.T.,"TOPCONN",TCGenQry(,,cQuery),cTrab,.F.,.T.)
			
			U_SYNCLOG(;
				'Agenda : ' +  SubStr(aSyncEnv[4],1,6) + CRLF +;
				'Tarefa : ' +  SubStr(aSyncEnv[4],7,6) + CRLF +;
				cLayout + ' - ' + AllTrim(aSyncEnv[17]) + CRLF +;
				'Query executada com sucesso: ' + CRLF + cQueryBkp)
			
			// Cria arquivo temporario
			
			fErase(Separa(cTempFile,'\',.T.)[3])
			DbCreate(cTempFile,aStru,__LocalDriver)
			DbUseArea(.T.,__LocalDriver,cTempFile,cTempTrab,.T.,.F.)
			
			U_SYNCLOG(;
				'Agenda : ' +  SubStr(aSyncEnv[4],1,6) + CRLF +;
				'Tarefa : ' +  SubStr(aSyncEnv[4],7,6) + CRLF +;
				'Arquivo Temporario criado com sucesso : ' + cTempFile)
			
			// --------------------------------------------------------------
			// Preenchimento do arquivo temporario
			// --------------------------------------------------------------
			
			(cTrab)->(DbGotop())
			
			Do While .Not. (cTrab)->(EOF())
				
				RecLock(cTempTrab,.T.)
				
				aOrigem	:=	StrTokArr(AllTrim((cTrab)->ORIGEM),'/',.T.)
				
				(cTempTrab)->POS0001 := (cTrab)->POS0001
				(cTempTrab)->POS0007 := (cTrab)->POS0007
				(cTempTrab)->POS0017 := U_SYNCFIL(aSyncEnv[7],5,AllTrim((cTrab)->POS0017)) + SubStr((cTrab)->POS0001,1,4) + (cTrab)->DOC
				(cTempTrab)->POS0057 := (cTrab)->POS0057
				(cTempTrab)->POS0058 := U_SYNCFIL(aSyncEnv[7],2,AllTrim((cTrab)->POS0058)) + U_SYNCFIL(aSyncEnv[7],3,AllTrim((cTrab)->POS0058))
				(cTempTrab)->POS0076 := (cTrab)->POS0076
				(cTempTrab)->POS0084 := (cTrab)->POS0084
				(cTempTrab)->POS0099 := '+' + StrTran(Transform((cTrab)->POS0099,'@E '+ Replicate('9',17) + '.' + Replicate('9',2)),' ','0')
				(cTempTrab)->POS0120 := (cTrab)->POS0120
				(cTempTrab)->POS0121 := (cTrab)->POS0121
				(cTempTrab)->POS0139 := aOrigem[1] + '/' + aOrigem[2]
				(cTempTrab)->POS0157 := (cTrab)->POS0157
				(cTempTrab)->POS0185 := (cTrab)->POS0185
				(cTempTrab)->POS0193 := (cTrab)->POS0193
				(cTempTrab)->POS0201 := (cTrab)->POS0201
				(cTempTrab)->POS0351 := (cTrab)->DOC + (cTrab)->POS0076 + (cTrab)->LOTE + (cTrab)->SUBLOTE + (cTrab)->LINHA + (cTrab)->POS0120
				(cTempTrab)->POS0401 := 'DIXTAL-' + SubStr(cPriDiaMes,1,4)
				(cTempTrab)->POS0451 := (cTrab)->POS0451
				(cTempTrab)->POS0479 := If(AllTrim((cTrab)->HIST_PADR) == cHistEnc,'E','N')
				(cTempTrab)->POS0480 := aOrigem[3]
				(cTempTrab)->POS0530 := If(Empty((cTrab)->POS0480),'N','P')
				
				MsUnLock()
				
				nCount++
				
				(cTrab)->(DbSkip())
				
			End Do
			
			// --------------------------------------------------------------
			
			U_SYNCDAT(cTempTrab,aSyncEnv,nCount)
			
			(cTrab)->(DbCloseArea())
			(cTempTrab)->(DbCloseArea())
			fErase(Separa(cTempFile,'\',.T.)[3])
			
		End IF
		
	Else
		
		U_SYNCLOG(;
			'Agenda : ' +  SubStr(aSyncEnv[4],1,6) + CRLF +;
			'Tarefa : ' +  SubStr(aSyncEnv[4],7,6) + CRLF +;
			'Nao foi possivel montar o ambiente de geracao do layout ' + cLayout +;
			If(Len(aEntPlan) == 2,'','Verifique o conteudo do parametro MV_SYNC002' + CRLF))
		
	End IF
	
	RpcClearEnv()
	
Return
